package com.link.base.base.system.utils;

import com.link.base.base.system.model.ConfigDb;
import com.link.core.util.StringUtils;
import com.link.base.core.basic.util.YmlConstants;
import com.link.core.cllog.LCLogger;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * jdbc工具类
 *
 * @author LiYang
 * @date 20190820
 */
public class JdbcUtil {

    /**
     * 数据库连接驱动
     */
    private static final String MYSQL_DRIVER_CLASS = "com.mysql.jdbc.Driver";

    /**
     * 匹配系统配置中数据库连接url，截取host+port+db
     */
    private static final Pattern PORT_DB_PATTERN = Pattern.compile("(?<=//).*(?=\\?)");

    /**
     * 数据库连接
     *
     * @author LiYang
     * @date 20190820
     * @param configDb
     * @return
     * @throws Exception
     */
    public static Connection getMySqlConnection(ConfigDb configDb) throws Exception {
        String urlPrefix = "jdbc:mysql://";
        String urlSuffix = "?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true&useAffectedRows=true";
        Class.forName(MYSQL_DRIVER_CLASS);
        StringBuilder sb = new StringBuilder(urlPrefix);
        sb.append(configDb.getHost());
        sb.append(":");
        sb.append(configDb.getPort());
        sb.append("/");
        sb.append(configDb.getDbName());
        Connection conn = DriverManager.getConnection(sb.toString(), configDb.getUsername(), configDb.getPw());
        return conn;
    }

    /**
     * 得到查询结果
     *
     * @author LiYang
     * @date 20190820
     * @param configDb
     * @param sql ：查询语句
     * @return
     */
    public static List<Map<String, Object>> getQueryResult(ConfigDb configDb, String sql) {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        List<Map<String, Object>> result = new ArrayList<>(64);
        try {
            conn = getMySqlConnection(configDb);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(meta.getColumnName(i), rs.getObject(i));
                }
                result.add(rowData);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            LCLogger.withException(e);
        } finally {
            if (null != rs) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    LCLogger.withException(e);
                }
            }
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    LCLogger.withException(e);
                }
            }
            if (null != conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    LCLogger.withException(e);
                }
            }
        }
        return result;
    }

    /**
     * 判断是否需要使用默认配置
     *
     * @author LiYang
     * @date 20190821
     * @param configDb
     */
    public static void changeDbConfig(ConfigDb configDb) {
        //判断传入的地址、端口、用户名、密码、库名是否为空，如果有一项为空，则使用默认
        boolean isDefault = StringUtils.isBlank(configDb.getHost()) || StringUtils.isBlank(configDb.getPort())
                || StringUtils.isBlank(configDb.getUsername()) || StringUtils.isBlank(configDb.getPw())
                || StringUtils.isBlank(configDb.getDbName());
        if (isDefault) {
            String url = YmlConstants.getDatasourceUrl();
            String username = YmlConstants.getDatasourceUsername();
            String pw = YmlConstants.getDatasourcePw();
            String hostPortDb = "";
            //从url中截取host+port+db
            Matcher matcher = PORT_DB_PATTERN.matcher(url);
            while (matcher.find()) {
                hostPortDb = matcher.group();
            }
            int colonIndex = hostPortDb.indexOf(":");
            int slashIndex = hostPortDb.indexOf("/");
            String host = hostPortDb.substring(0, colonIndex);
            String port = hostPortDb.substring(colonIndex + 1, slashIndex);
            String db = hostPortDb.substring(slashIndex + 1, hostPortDb.length());
            configDb.setHost(host);
            configDb.setPort(port);
            configDb.setDbName(db);
            configDb.setUsername(username);
            configDb.setPw(pw);
        }
    }

    public static void main(String[] args) {
        String url = "jdbc:mysql://192.168.12.94:25006/linkclouduat?useUnicode=true&characterEncoding="
                + "utf-8&allowMultiQueries=true&useSSL=true&useAffectedRows=true";
        Matcher matcher = PORT_DB_PATTERN.matcher(url);
        String hostPortDb = "";
        while (matcher.find()) {
            hostPortDb = matcher.group();
        }
        int colonIndex = hostPortDb.indexOf(":");
        int slashIndex = hostPortDb.indexOf("/");
        String host = hostPortDb.substring(0, colonIndex);
        String port = hostPortDb.substring(colonIndex + 1, slashIndex);
        String db = hostPortDb.substring(slashIndex + 1, hostPortDb.length());
        System.out.println(host);
        System.out.println(port);
        System.out.println(db);
    }
}
